package cn.lysj.framework.customer.repository;

import cn.lysj.framework.base.BaseRepository;
import cn.lysj.framework.customer.domain.CustomerVipCoupon;
import cn.lysj.framework.customer.vo.CustomerVipCouponVo;
import org.springframework.data.jpa.repository.Query;

import javax.xml.crypto.Data;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * @author zwj
 * @description 用户优惠券数据处理层
 * @create 2019-01-15 15:44:14
 **/
public interface CustomerVipCouponRepository extends BaseRepository<CustomerVipCoupon> {

    List<CustomerVipCoupon> findByCustomerVipIdAndCouponId(String customerVipId,String couponId);
    //按vipId查找，先按1.使用状态升序 2.创建时间降序
    @Query(value = "select new cn.lysj.framework.customer.vo.CustomerVipCouponVo(b.name,b.fullReductionMoney,b.discountMoney,a.id,a.useType,a.beginDate,a.endDate) from CustomerVipCoupon a,Coupon b where a.couponId = b.id and a.customerVipId = ?1 order by a.useType asc , a.createTime desc")
    List<CustomerVipCouponVo> findCustomerVipAllCoupon(String customerVipId);
    //查询用户该金额下可以使用的优惠券信息
    @Query(value = "select new cn.lysj.framework.customer.vo.CustomerVipCouponVo(b.name,b.fullReductionMoney,b.discountMoney,a.id,a.useType,a.beginDate,a.endDate) from CustomerVipCoupon a,Coupon b where a.couponId = b.id and a.customerVipId = ?1 and b.fullReductionMoney <= ?2 and ?3 between a.beginDate and a.endDate and a.useType = 1")
    List<CustomerVipCouponVo> getCustomerVipCouponForUse(String customerVipId, BigDecimal useMoney, Date useDate);

    //查询用户可以使用的优惠券
    @Query(value = "select new cn.lysj.framework.customer.vo.CustomerVipCouponVo(b.name,b.fullReductionMoney,b.discountMoney,a.id,a.useType,a.beginDate,a.endDate) " +
            "from CustomerVipCoupon a,Coupon b where a.couponId = b.id and a.customerVipId = ?1 and a.useType = ?2 and a.endDate > ?3 order by a.createTime desc")
    List<CustomerVipCouponVo> findCustomerVipCouponInValid(String customerVipId, Integer useType, Date nowTime);
    //查询用户已经使用的优惠券
    @Query(value = "select new cn.lysj.framework.customer.vo.CustomerVipCouponVo(b.name,b.fullReductionMoney,b.discountMoney,a.id,a.useType,a.beginDate,a.endDate) " +
            "from CustomerVipCoupon a,Coupon b where a.couponId = b.id and a.customerVipId = ?1 and a.useType = ?2 order by a.createTime desc")
    List<CustomerVipCouponVo> findCustomerVipAllCouponInUsed(String customerVipId,Integer useType);
    //查询用户可以使用但是过期的优惠券
    @Query(value = "select new cn.lysj.framework.customer.vo.CustomerVipCouponVo(b.name,b.fullReductionMoney,b.discountMoney,a.id,a.useType,a.beginDate,a.endDate) " +
            "from CustomerVipCoupon a,Coupon b where a.couponId = b.id and a.customerVipId = ?1 and a.useType = ?2 and a.endDate < ?3 order by a.createTime desc")
    List<CustomerVipCouponVo> findCustomerVipAllCouponInPastDue(String customerVipId,Integer useType,Date nowTime);
    //查询某张可以使用的优惠券 符合时间在有效期内 金额大于满减金额
    @Query(value = "select new cn.lysj.framework.customer.vo.CustomerVipCouponVo(b.name,b.fullReductionMoney,b.discountMoney,a.id,a.useType,a.beginDate,a.endDate) " +
            "from CustomerVipCoupon a,Coupon b where a.couponId = b.id and a.id = ?1 and a.useType = ?2 and  ?3 between a.beginDate and a.endDate and b.fullReductionMoney <= ?4")
    CustomerVipCouponVo findAssignCustomerVipCouponVo(String customerVipCouponId, Integer useType, Date nowTime, BigDecimal useMoney);

}
